
IF EXISTS (SELECT 1
          FROM SYSOBJECTS
          WHERE  id = OBJECT_ID('USP_GetDepartmentByPrivilege1')
          AND TYPE IN ('P','PC'))
DROP PROCEDURE USP_GetDepartmentByPrivilege1
GO

/****** Object:  StoredProcedure [dbo].[USP_GetDepartmentByPrivilege]    Script Date: 12/18/2010 01:09:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		AKE
-- Create date: 11:49 13/9/2553
-- =============================================
CREATE PROCEDURE [dbo].[USP_GetDepartmentByPrivilege1]
	-- Add the parameters for the stored procedure here
	@group_user_type int,
	@department_code int,
	@master_dept_level int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Modified by Boonyarit Yingvoragan
    -- Modified date 7/11/2553
	declare @master_department_level as int
	declare @table as table(department_code int)
	declare @level as int

	select @master_department_level = mas.master_department_level 
	from stp_department dep 
	inner join master_department mas on dep.master_department_code = mas.master_department_code
	where department_code = @department_code
	
	if @department_code = 0
	begin
		insert @table 
		select department_code from stp_department where department_code > @department_code
	end
	else
	begin
		insert @table 
		select department_code from stp_department where department_code = @department_code
	end
		
	set @level = @master_department_level
	while @level < 10
	begin
		insert @table 
		select department_code 
		from stp_department 
		where department_parent in (select department_code from @table) 
		
		set @level = @level + 1
	end

	select		
			SeqNo = ROW_NUMBER() over(order by mas.master_department_level),
			stp.department_code,
			stp.department_name,
			stp.department_parent ,
			mas.master_department_level
	from	master_department as mas
	join	stp_department as stp on mas.master_department_code = stp.master_department_code
	left join stp_department as p_stp on stp.department_parent = p_stp.department_code
	where mas.master_department_level >= @master_department_level and
			stp.department_status = 1 and
			stp.department_code in (select department_code from @table where department_code is not null and department_code > 0) and
			@master_dept_level in (0,mas.master_department_level)
	order by mas.master_department_level

END

GO